﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Xml;
using System.Drawing;
using System.IO;

namespace AuctionDataService
{
    //TODO
    //finish implementation
    //put strings in a resource
    //logging
    //good practice connections

    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    public class Service1 : System.Web.Services.WebService
    {
        //various reusable private variables
        //connection string is obtained by adding a data source
        private string connectionString = "Data Source=MR-DEMON\\SQLEXPRESS;Initial Catalog=AuctionSite;Integrated Security=True;";
        private SqlConnection conn = null;
        private DataSet dataset = null;
        private SqlDataAdapter adapter = null;

        private void LogError(Exception ex)
        {
            //TODO
            //should log error
        }

        //users
        [WebMethod(Description="Add a user to the system")]
        public int InsertUser(string name, string username, string password, string address, string billing, string email)
        {
            String command = "SELECT * FROM users;";
            String commandins = @"INSERT INTO users (name,username,password,address,billing,email) VALUES (@name,@username,@password,@address,@billing,@email);";
            DataTable dt;
            int rows = 0;
            conn = new SqlConnection(connectionString);
            try
            {
                conn.Open();
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand(command, conn); ;
                dataset = new DataSet();
                adapter.Fill(dataset);
                dt = dataset.Tables[0];
                DataRow newRow = dt.NewRow();
                newRow["name"] = name;
                newRow["username"] = username;
                newRow["password"] = password;
                newRow["address"] = address;
                newRow["billing"] = billing;
                newRow["email"] = email;
                dt.Rows.Add(newRow);
                SqlCommand cmd = new SqlCommand(commandins, conn);
                cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50, "name");
                cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50, "username");
                cmd.Parameters.Add("@password", SqlDbType.NVarChar, 50, "password");
                cmd.Parameters.Add("@id", SqlDbType.Int, 10, "id");
                cmd.Parameters.Add("@address", SqlDbType.NVarChar, 50, "address");
                cmd.Parameters.Add("@billing", SqlDbType.NVarChar, 50, "billing");
                cmd.Parameters.Add("@email", SqlDbType.NVarChar, 50, "email");
                adapter.InsertCommand = cmd;
                rows = adapter.Update(dataset);
                dataset.AcceptChanges();
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                if (conn!=null)
                    conn.Close();
            }
            return rows;
        }

        [WebMethod(Description="Return true if validation was successful")]
        public bool VerifyUser(string username, string password)
        {
            String command = "SELECT COUNT(*) FROM users WHERE username=@username AND password=@password;";
            SqlCommand sqlCommand;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                sqlCommand = new SqlCommand(command, conn);
                sqlCommand.Parameters.Add("@username", System.Data.SqlDbType.VarChar, 50).Value = username;
                sqlCommand.Parameters.Add("@password", System.Data.SqlDbType.VarChar, 50).Value = password;
                Object result = sqlCommand.ExecuteScalar();
                if (result != null) return true;
                    return false;
            }
            catch (Exception ex)
            {
                LogError(ex);
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        [WebMethod]
        public DataTable ListAuctionsByUser(int userId)
        {
            String command = "SELECT * FROM auctions WHERE auction_id = ANY(SELECT auction_id FROM watched WHERE user_id = @user)";
            DataTable dt = new DataTable();
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                adapter = new SqlDataAdapter();
                SqlCommand sqlCommand = new SqlCommand(command, conn); 
                sqlCommand.Parameters.Add("@user", SqlDbType.Int).Value = userId;
                adapter.SelectCommand = sqlCommand;
                dataset = new DataSet();
                adapter.Fill(dataset);
                dt = dataset.Tables[0];
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        //auctions
        [WebMethod]
        public DataTable ListAuctions()
        {
            String command = "SELECT * FROM auctions;";
            DataTable dt = new DataTable();
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand(command, conn); ;
                dataset = new DataSet();
                adapter.Fill(dataset);
                dt = dataset.Tables[0];
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        [WebMethod]
        public int UpdateBid(int id, int bid, int buyerId)
        {
            String command = @"UPDATE auctions SET bid_level = @bid, buyer_id = @buyer WHERE auction_id = @id";
            SqlCommand sqlCommand;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                sqlCommand = new SqlCommand(command, conn);
                sqlCommand.Parameters.Add("@bid", System.Data.SqlDbType.VarChar, 50).Value = bid;
                sqlCommand.Parameters.Add("@buyer", System.Data.SqlDbType.Int).Value = buyerId;
                sqlCommand.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = id;
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                conn.Close();
            }  
            return 0;
        }

        [WebMethod]
        public int WatchAuction(int auction_id, int user_id)
        {
            String command = @"INSERT INTO watched (auction_id, user_id) VALUES @auction_id, @user_id";
            SqlCommand sqlCommand;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                sqlCommand = new SqlCommand(command, conn);
                sqlCommand.Parameters.Add("@auction_id", SqlDbType.Int).Value = auction_id;
                sqlCommand.Parameters.Add("@user_id", SqlDbType.Int).Value = user_id;
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                conn.Close();
            }
            return 0;
        }

        [WebMethod]
        public int UnWatchAuction(int auction_id, int user_id)
        {
            String command = @"DELETE * FROM watched WHERE auction_id = @auction_id";
            SqlCommand sqlCommand;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                sqlCommand = new SqlCommand(command, conn);
                sqlCommand.Parameters.Add("@auction_id", SqlDbType.Int).Value = auction_id;
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                conn.Close();
            }
            return 0;
        }


        [WebMethod(Description="Adds an auction")]
        public int AddAuction(string product_name, int bid_level, string final_date, int category, byte[] image_data, int seller_id)
        {
            MemoryStream ms;
            FileStream fs;
            try
            {
                ms = new MemoryStream(image_data);
                fs = new FileStream("./filetest.gif", FileMode.Create, FileAccess.ReadWrite);
                ms.WriteTo(fs);
                ms.Close();
                fs.Close();
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            String command = @"INSERT INTO auctions (bid_level, final_date, product_name, category, image, active, seller_id) VALUES (@bid_level, @final_date, @product_name, @category, @image, @active, @seller_id)";
            SqlCommand sqlCommand;
            int result = 0;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                sqlCommand = new SqlCommand(command, conn);
                sqlCommand.Parameters.Add("@bid_level", SqlDbType.Int).Value = bid_level;
                sqlCommand.Parameters.Add("@final_date", SqlDbType.VarChar, 50).Value = final_date;
                sqlCommand.Parameters.Add("@product_name", SqlDbType.VarChar, 50).Value = product_name;
                sqlCommand.Parameters.Add("@category", SqlDbType.Int).Value = category;
                sqlCommand.Parameters.Add("@image", SqlDbType.VarChar, 50).Value = "etc";
                sqlCommand.Parameters.Add("@active", SqlDbType.Int).Value = 1;
                sqlCommand.Parameters.Add("@seller_id", SqlDbType.Int).Value = 1;
                result = sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        //categories
        [WebMethod]
        public string GetCatTree()
        {
            //todo 
            //testing
            String command = "SELECT * FROM categories;";
            DataTable dt = new DataTable();
            XmlDocument doc = new XmlDocument();
            XmlElement root = doc.CreateElement("root");
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand(command, conn);
                dataset = new DataSet();
                adapter.Fill(dataset);
                dt = dataset.Tables[0];
                doc.AppendChild(root);

                foreach (DataRow row in dt.Rows)
                {
                    if (row["parent"].ToString() == "")
                    {
                        XmlElement el = (XmlElement)root.AppendChild(doc.CreateElement("node"));
                        el.SetAttribute("cat_id", row["cat_id"].ToString());
                        el.SetAttribute("cat_name", row["cat_name"].ToString());
                        el.SetAttribute("parent", row["parent"].ToString());
                    }
                    else
                    {
                        XmlNodeList nodeList = doc.SelectNodes("/root/node[@cat_id='" + row["parent"].ToString() + "']");
                        XmlElement parent = (XmlElement)nodeList.Item(0);
                        XmlElement el = (XmlElement)parent.AppendChild(doc.CreateElement("node"));
                        el.SetAttribute("cat_id", row["cat_id"].ToString());
                        el.SetAttribute("cat_name", row["cat_name"].ToString());
                        el.SetAttribute("parent", row["parent"].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
            finally
            {
                conn.Close();
            }
            return doc.InnerXml;
        }
    }
}





